﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassFolio.Dict;
using System.Data.OracleClient;
using System.Data;
using System.Globalization;
using ClassFolio;
using ClassFolio.Model;

namespace OraFolio.Gates
{
	public class OraDeptGate : IDeptGate
	{
		public void RemoveDept(int activeUserId, int deptId)
		{
			OraDB.CheckConnection();
			string sql = "begin folio.dict_pkg.dept_del(:1, :2); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = activeUserId;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = deptId;
			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.dp_remove, err);
			}
		}
		public Dept ApplyToDb(int activeUserId, Dept dept)
		{
			if (dept.ID != 0)
				return DeptUpdate(activeUserId, dept);
			else
				return DeptInsert(activeUserId, dept);
		}
		public Dept RefreshFromDb(Dept dept)
		{
			OraDB.CheckConnection();
			string sql = " select d.short_name, d.full_name from folio.departments d where d.id = :1";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = dept.ID;

			OracleDataReader reader = cmd.ExecuteReader();
			try
			{
				while (reader.Read())
				{
					dept.ShortName = reader["short_name"].ToString();
					dept.FullName = reader["full_name"].ToString();
				}
				return dept;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.dp_refresh, err);
			}
			finally
			{
				reader.Close();
			}
		}
		public bool DeptExist(int deptId)
		{
			OraDB.CheckConnection();
			string sql = " select count(*) cnt from folio.departments where id = :1";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = deptId;

			OracleDataReader reader = cmd.ExecuteReader();
			try
			{
				if (reader.Read())
					return Convert.ToInt32(reader["cnt"], CultureInfo.InvariantCulture) == 1;
				else
					return false;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.dp_exist, err);
			}
			finally
			{
				reader.Close();
			}
		}

		public Sequence GetSequence(SqlSwitch swth)
		{
			Sequence sequence = new Sequence();
			sequence.Sql = " SELECT NMB, ID, SHORT_NAME, FULL_NAME FROM V_DEPTS WHERE 1=1";
			HashSet<string> fields = new HashSet<string>() {
                "ID", "SHORT_NAME", "FULL_NAME"
            };

			if (swth != null)
				sequence.SqlSwitch.Condition = swth.Condition.Where(i => fields.Contains(i.Name.ToUpper())).ToList();

			sequence.Index = 0;
			sequence.RecordCount = Adjustment.Default.CountRecordDept;

			return sequence;
		}

		// Private
		private static Dept DeptInsert(int activeUserId, Dept dept)
		{
			OraDB.CheckConnection();
			string sql = "begin :1 := folio.dict_pkg.dept_ins(:2, :3, :4); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Output;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = activeUserId;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = dept.ShortName;
			cmd.Parameters.Add("4", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["4"].Value = dept.FullName;

			try
			{
				cmd.ExecuteNonQuery();
				return Dept.LoadDept(Convert.ToInt32(cmd.Parameters["1"].Value, CultureInfo.InvariantCulture));
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.dp_insert, err);
			}
		}
		private static Dept DeptUpdate(int activeUserId, Dept dept)
		{
			OraDB.CheckConnection();
			string sql = "begin folio.dict_pkg.dept_upd(:1, :2, :3, :4); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = activeUserId;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = dept.ID;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = dept.ShortName;
			cmd.Parameters.Add("4", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["4"].Value = dept.FullName;

			try
			{
				cmd.ExecuteNonQuery();
				return dept;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.dp_update, err);
			}
		}
	}
}
